package SqlHelper;

public class SqlService {
    public String getName(String key)
    {
        return "SELECT\n" +
                "\ttemp.staff_name AS staffName,\n" +
                "\tSUM(temp.sumAmount) AS dealGMV\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tss.staffId,\n" +
                "\t\t\tss.staffName AS staff_name,\n" +
                "\t\t\tsdr.dealerId AS dealer_id,\n" +
                "\t\t\tzd.dealer_name,\n" +
                "\t\t\tsdr.`status`,\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tSUM(zsop.amount)\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tzscartm1jucom2.zscar_sales_order_paymentdocument zsop\n" +
                "\t\t\t\tLEFT JOIN zscartm1jucom2.zscar_sales_order zso ON zso.sales_id = zsop.sales_id\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tzso.dealer_id = zd.dealer_id\n" +
                "\t\t\t\tAND zsop.pay_status = 1\n" +
                "\t\t\t\tAND from_unixtime(zsop.create_time,'%Y/%m/%d') BETWEEN '2019/01/14' and '2019/01/21'\n" +
                "\t\t\t) AS sumAmount\n" +
                "\t\tFROM\n" +
                "\t\t\tzscartm1jucom2.crm_sales_staff ss\n" +
                "\t\tLEFT JOIN zscartm1jucom2.crm_staff_dealer sdr ON ss.staffId = sdr.staffId\n" +
                "\t\tLEFT JOIN zscartm1jucom2.zscar_dealer zd ON sdr.dealerId = zd.dealer_id\n" +
                "\t) temp\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND temp. STATUS = #{status}\n" +
                "GROUP BY\n" +
                "\ttemp.staffId\n" +
                "ORDER BY\n" +
                "\tdealGMV DESC";
    }
}
